package it.utils;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import it.domain.Userbook;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Properties;

/**
 * @author wsx
 * @create 2020-03-26-11:30
 */
public class DBUtils {
    QueryRunner queryRunner=new QueryRunner();
    //1.定义成员变量DataSource
    private static DataSource ds;

    static {
        try {
            //1.加载配置文件
            Properties properties = new Properties();
            properties.load(DBUtils.class.getClassLoader().getResourceAsStream("db.properties"));
            //2.获取DataSource
            ds = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取连接对象
     */
    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }

    /**
     * 获取连接池
     */
    public static DataSource getDataSource(){
        return ds;
    }

    /**
     * 释放资源
     */
    public static void close(Statement stmt, Connection conn){
        close(null,stmt,conn);
    }

    public static void close(ResultSet rs, Statement stmt, Connection conn){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(stmt!=null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(conn!=null){//归还连接
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public List<Userbook> getAUBList(String user, Integer currentPage, Integer counts) throws SQLException {
        String sql = "select * from userbook where username =? order by id desc limit ?,?";
//        System.out.println(sql);
        List<Userbook> list = queryRunner.query(sql, new BeanListHandler<Userbook>(Userbook.class),
                user, (currentPage -1) * counts, counts);
        System.out.println(sql);
        return list;
    }
}
